
Imports System
Imports System.Collections
Imports System.Data.SqlClient
Imports System.Diagnostics
Imports _2bMatesSite.Common

Namespace _2bMatesSite.BO
	''' <summary>
	''' Summary description for ContentItem.
	''' </summary>
	Public Class ContentItem
		Public ID As Integer
		Protected specialPageID As Integer
		Protected userID As Integer
		Protected groupID As Integer
		Protected linkName As String
		Protected linkURL As String
		Protected linkTypeID As Integer
		Protected content As String
		Protected itemDate As DateTime

		Public Sub New()
			ID = -1
			specialPageID = -1
			userID = -1
			groupID = -1
			linkName = ""
			linkURL = ""
			linkTypeID = -1
			content = ""
			itemDate = DateTime.Now
		End Sub

		Public Sub New(ByVal dr As SqlDataReader)
			LoadFromReader(dr)
		End Sub

		Public Property SpecialPageID() As Integer
			Get
				Return specialPageID
			End Get

			Set
				specialPageID = value
			End Set
		End Property

		Public Property UserID() As Integer
			Get
				Return userID
			End Get

			Set
				userID = value
			End Set
		End Property

		Public Property GroupID() As Integer
			Get
				Return groupID
			End Get

			Set
				groupID = value
			End Set
		End Property

		Public Property LinkName() As String
			Get
				Return linkName
			End Get

			Set
				linkName = value
			End Set
		End Property

		Public Property LinkURL() As String
			Get
				Return linkURL
			End Get

			Set
				linkURL = value
			End Set
		End Property

		Public Property LinkTypeID() As Integer
			Get
				Return linkTypeID
			End Get

			Set
				linkTypeID = value
			End Set
		End Property

		Public Property Content() As String
			Get
				Return content
			End Get

			Set
				content = value
			End Set
		End Property

		Public ReadOnly Property ItemDate() As DateTime
			Get
				Return itemDate
			End Get
		End Property

		Public Sub Save()
			If ID <= 0 Then
				DoInsert()
			Else
				DoUpdate()
			End If
		End Sub

		Private Sub DoInsert()
			Dim conn As SqlConnection = DBManager.GetUnmanagedConnection()
			Dim tran As SqlTransaction = conn.BeginTransaction()

			Try
				' Insert the content item
				Dim cmd As New SqlCommand(GetInsertSQL())
				cmd.Connection = conn
				cmd.Transaction = tran

				PopulateCommand(cmd)

				Dim rows As Integer = cmd.ExecuteNonQuery()
				Debug.Assert(rows = 1)

				tran.Commit()
			Catch e As Exception
				tran.Rollback()
				Throw e
			Finally
				conn.Close()
			End Try
		End Sub

		Protected Sub DoUpdate()
			Dim conn As SqlConnection = DBManager.GetUnmanagedConnection()

			Try
				Dim cmd As New SqlCommand(GetUpdateSQL())
				cmd.Connection = conn

				PopulateCommand(cmd)

				cmd.Parameters.Add("@ID", ID)

				Dim rows As Integer = cmd.ExecuteNonQuery()
				Debug.Assert(rows = 1)
			Finally
				conn.Close()
			End Try
		End Sub

		Public Sub DoDelete()
			Dim conn As SqlConnection = DBManager.GetUnmanagedConnection()

			Try
				' Delete the content item
				Dim cmd As New SqlCommand(GetDeleteSQL())
				cmd.Connection = conn

				cmd.Parameters.Add("@ID", ID)

				Dim rows As Integer = cmd.ExecuteNonQuery()
				Debug.Assert(rows = 1)
			Finally
				conn.Close()
			End Try
		End Sub

		Public Function Comments() As ArrayList
			Return GetComments()
		End Function

		Private Shared Function GetInsertSQL() As String
			Dim sql As String = "INSERT INTO SpecialPageContent" + Environment.NewLine
			sql += "( SpecialPageID, UID, GroupID, LinkName, LinkURL, LinkTypeID, Content )" + Environment.NewLine
			sql += "VALUES" + Environment.NewLine
			sql += "( @SpecialPageID, @UserID, @GroupID, @LinkName, @LinkURL, @LinkTypeID, @Content )" + Environment.NewLine
			Return sql
		End Function

		Private Shared Function GetUpdateSQL() As String
			Dim sql As String = "UPDATE SpecialPageContent SET" + Environment.NewLine
			sql += "" & Chr(9) & "UID=@UserID," + Environment.NewLine
			sql += "" & Chr(9) & "GroupID=@GroupID," + Environment.NewLine
			sql += "" & Chr(9) & "LinkName=@LinkName," + Environment.NewLine
			sql += "" & Chr(9) & "LinkURL=@LinkURL," + Environment.NewLine
			sql += "" & Chr(9) & "LinkTypeID=@LinkTypeID," + Environment.NewLine
			sql += "" & Chr(9) & "Content=@Content" + Environment.NewLine
			sql += "WHERE" + Environment.NewLine
			sql += "" & Chr(9) & "ID=@ID" + Environment.NewLine
			Return sql
		End Function

		Private Shared Function GetDeleteSQL() As String
			Dim sql As String = "DELETE FROM SpecialPageContent" + Environment.NewLine
			sql += "WHERE" + Environment.NewLine
			sql += "" & Chr(9) & "ID=@ID" + Environment.NewLine
			Return sql
		End Function

		Private Sub PopulateCommand(ByVal cmd As SqlCommand)
			DBManager.SetNZValue(cmd, "@SpecialPageID", specialPageID)
			DBManager.SetNZValue(cmd, "@UserID", userID)
			DBManager.SetNZValue(cmd, "@GroupID", groupID)
			cmd.Parameters.Add("@LinkName", LinkName)
			cmd.Parameters.Add("@LinkURL", LinkURL)
			DBManager.SetNZValue(cmd, "@LinkTypeID", LinkTypeID)
			cmd.Parameters.Add("@Content", content)
		End Sub

		Public Shared Function GetContentItemByID(ByVal id As Integer) As ContentItem
			Dim cmd As New SqlCommand("SELECT * FROM SpecialPageContent WHERE ID=@ID")
			cmd.Parameters.Add("@ID", id)

			Return GetContentItemByCmd(cmd)
		End Function

		' Retrieve the content items using the given SQL command object
		' return null if no special page found
		Protected Shared Function GetContentItemByCmd(ByVal cmd As SqlCommand) As ContentItem
			Dim li As ContentItem = Nothing

			Dim conn As SqlConnection = DBManager.GetUnmanagedConnection()

			Try
				cmd.Connection = conn
				Dim dr As SqlDataReader = cmd.ExecuteReader()

				If dr.Read() Then
					li = New ContentItem(dr)
					Debug.Assert(Not dr.Read())
				End If

				dr.Close()
			Finally
				conn.Close()
			End Try

			Return li
		End Function

		Public Shared Function GetCommentByID(ByVal id As Integer) As ContentItemComment
			Dim cmd As New SqlCommand("SELECT * FROM ContentComment WHERE ID=@ID")
			cmd.Parameters.Add("@ID", id)

			Return GetCommentByCmd(cmd)
		End Function

		' Retrieve the content element comment using the given SQL command object
		' return null if no special page found
		Protected Shared Function GetCommentByCmd(ByVal cmd As SqlCommand) As ContentItemComment
			Dim lic As ContentItemComment = Nothing

			Dim conn As SqlConnection = DBManager.GetUnmanagedConnection()

			Try
				cmd.Connection = conn
				Dim dr As SqlDataReader = cmd.ExecuteReader()

				If dr.Read() Then
					lic = New ContentItemComment(dr)
					Debug.Assert(Not dr.Read())
				End If

				dr.Close()
			Finally
				conn.Close()
			End Try

			Return lic
		End Function

		Protected Sub LoadFromReader(ByVal dr As SqlDataReader)
			ID = dr.GetInt32(dr.GetOrdinal("ID"))

			specialPageID = dr.GetInt32(dr.GetOrdinal("SpecialPageID"))

			userID = DBManager.GetNZInt32(dr, "UID")

			groupID = DBManager.GetNZInt32(dr, "GroupID")

			linkName = DBManager.GetNZString(dr, "LinkName")

			linkURL = DBManager.GetNZString(dr, "LinkURL")

			linkTypeID = DBManager.GetNZInt32(dr, "LinkTypeID")

			content = DBManager.GetNZString(dr, "Content")

			itemDate = DBManager.GetNZDateTime(dr, "Date")
		End Sub

		Public Class ContentItemComment
			Protected commentID As Integer
			Protected specialPageContentID As Integer
			Protected fromUserID As Integer
			Protected comment As String
			Protected [date] As DateTime

			Public Sub New(ByVal specialPageContentID As Integer, ByVal fromUserID As Integer, ByVal comment As String)
				commentID = -1
				Me.specialPageContentID = specialPageContentID
				Me.fromUserID = fromUserID
				Me.comment = comment
				[date] = DateTime.Now
			End Sub

			Public Sub New(ByVal dr As SqlDataReader)
				commentID = dr.GetInt32(dr.GetOrdinal("ID"))
				comment = dr.GetString(dr.GetOrdinal("Comment"))
				specialPageContentID = dr.GetInt32(dr.GetOrdinal("SpecialPageContentID"))
				fromUserID = dr.GetInt32(dr.GetOrdinal("UID"))
				[date] = dr.GetDateTime(dr.GetOrdinal("Date"))
			End Sub

			Public ReadOnly Property ID() As Integer
				Get
					Return commentID
				End Get
			End Property

			Public ReadOnly Property SpecialPageContentID() As Integer
				Get
					Return specialPageContentID
				End Get
			End Property

			Public ReadOnly Property PostedByUserID() As Integer
				Get
					Return fromUserID
				End Get
			End Property

			Public ReadOnly Property Comment() As String
				Get
					Return comment
				End Get
			End Property

			Public ReadOnly Property [Date]() As DateTime
				Get
					Return [date]
				End Get
			End Property

			Public Sub Save()
				DoInsert()
			End Sub

			Private Sub DoInsert()
				Dim conn As SqlConnection = DBManager.GetUnmanagedConnection()
				Dim tran As SqlTransaction = conn.BeginTransaction()

				Try
					' Insert the content element comment
					Dim cmd As New SqlCommand(GetInsertSQL())
					cmd.Connection = conn
					cmd.Transaction = tran

					PopulateCommand(cmd)

					Dim rows As Integer = cmd.ExecuteNonQuery()
					Debug.Assert(rows = 1)

					' Reload the content element comment
					DoRefresh(tran)

					tran.Commit()
				Catch e As Exception
					tran.Rollback()
					Throw e
				Finally
					conn.Close()
				End Try
			End Sub

			Public Sub DoDelete()
				Dim conn As SqlConnection = DBManager.GetUnmanagedConnection()

				Try
					' Delete the content element comment
					Dim cmd As New SqlCommand(GetDeleteSQL())
					cmd.Connection = conn

					cmd.Parameters.Add("@ID", ID)

					Dim rows As Integer = cmd.ExecuteNonQuery()
					Debug.Assert(rows = 1)
				Finally
					conn.Close()
				End Try
			End Sub

			Protected Sub DoRefresh(ByVal sqlTrans As SqlTransaction)
				Dim conn As SqlConnection = Nothing
				Dim ownConnection As Boolean = False

				If sqlTrans = Nothing Then
					conn = DBManager.GetUnmanagedConnection()
					ownConnection = True
				Else
					conn = sqlTrans.Connection
				End If

				Try
					Dim cmd As New SqlCommand("SELECT * FROM ContentComment WHERE SpecialPageContentID = @specialPageContentID")
					cmd.Parameters.Add("@specialPageContentID", specialPageContentID)

					cmd.Connection = conn
					If Not ownConnection Then
						cmd.Transaction = sqlTrans
					End If

					Dim dr As SqlDataReader = cmd.ExecuteReader()

					Dim found As Boolean = dr.Read()
					Debug.Assert(found)
					LoadFromReader(dr)

					dr.Close()
				Finally
					If ownConnection Then
						conn.Close()
					End If
				End Try
			End Sub

			Protected Sub LoadFromReader(ByVal dr As SqlDataReader)
				specialPageContentID = dr.GetInt32(dr.GetOrdinal("SpecialPageContentID"))

				fromUserID = dr.GetInt32(dr.GetOrdinal("UID"))

				comment = DBManager.GetNZString(dr, "Comment")
			End Sub

			Private Shared Function GetInsertSQL() As String
				Dim sql As String = "INSERT INTO ContentComment" + Environment.NewLine
				sql += "( UID, SpecialPageContentID, Comment )" + Environment.NewLine
				sql += "VALUES" + Environment.NewLine
				sql += "( @UID, @SpecialPageContentID, @Comment )" + Environment.NewLine
				Return sql
			End Function

			Private Shared Function GetDeleteSQL() As String
				Dim sql As String = "DELETE FROM ContentComment" + Environment.NewLine
				sql += "WHERE" + Environment.NewLine
				sql += "" & Chr(9) & "ID=@ID" + Environment.NewLine
				Return sql
			End Function

			Private Sub PopulateCommand(ByVal cmd As SqlCommand)
				DBManager.SetNZValue(cmd, "@UID", fromUserID)
				DBManager.SetNZValue(cmd, "@SpecialPageContentID", specialPageContentID)
				cmd.Parameters.Add("@Comment", comment)
			End Sub
		End Class

		Protected Function GetComments() As ArrayList
			Dim cl As New ArrayList()

			Dim conn As SqlConnection = DBManager.GetUnmanagedConnection()

			Try
				Dim cmd As New SqlCommand("SELECT * FROM ContentComment WHERE SpecialPageContentID=@SpecialPageContentID ORDER BY DATE DESC")
				cmd.Connection = conn
				cmd.Parameters.Add("@SpecialPageContentID", ID)

				Dim dr As SqlDataReader = cmd.ExecuteReader()
				While dr.Read()
					cl.Add(New ContentItemComment(dr))
				End While
				dr.Close()
			Finally
				conn.Close()
			End Try

			Return cl
		End Function
	End Class
End Namespace






